/*
Retrieves a list of Content Records owned by the specified contact with the specified status. Security IS enforced.

Returns:
Everything from the DocumentMain table except the blob. Also includes ContentAuthorityGroupKey and OwnerGroupMemberKey
from ContentWorkflowParameters if Workflow is being used and HierarchyKey from the Hierarchy element representing the document.
*/
CREATE PROC [dbo].[asi_ContentListByOwnerContactKey]
   @contentProducerKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
   @documentStatus int,
   @expiringSoon bit = 0
AS
BEGIN

   DECLARE @conWarningDays1 int
   SELECT @conWarningDays1 = CONVERT(int, ParameterValue) FROM SystemConfig WHERE ParameterName = 'CM.ExpirationWarningDays1'
   IF (@conWarningDays1 IS NULL)
   BEGIN
      SELECT @conWarningDays1 = CONVERT(int, ParameterValue) FROM SystemConfig WHERE ParameterName = 'CM.ExpirationWarningDays2'
   END
   IF (@conWarningDays1 IS NULL)
   BEGIN
      SET @conWarningDays1 = 10
   END

   SELECT a.[HierarchyKey],
          b.[DocumentKey],
          b.[DocumentVersionKey],
          b.[DocumentStatusCode],
          b.[DocumentName],
          b.[AlternateName],
          b.[DocumentDescription],
          b.[DocumentTypeCode],
          b.[IsSystem],
          b.[ContainsChildrenFlag],
          b.[RelatedDocumentVersionKey],
          b.[AccessKey],
          b.[DefaultChildAccessKey],
          b.[StatusUpdatedOn],
          b.[StatusUpdatedByUserKey],
          b.[CreatedOn],
          b.[CreatedByUserKey],
          b.[UpdatedOn],
          b.[UpdatedByUserKey],
          c.[ContentAuthorityGroupKey],
          c.[OwnerGroupMemberKey]
     FROM [dbo].[Hierarchy] a INNER JOIN [dbo].[DocumentMain] b ON a.[UniformKey] = b.[DocumentVersionKey]
      INNER JOIN [dbo].[ContentWorkflowParameters] c ON b.[DocumentVersionKey] = c.[DocumentVersionKey]
      INNER JOIN [dbo].[GroupMember] d ON c.[ContentAuthorityGroupKey] = d.[GroupKey]
    WHERE b.[DocumentStatusCode] = @documentStatus
      AND b.[DocumentTypeCode] = 'CON'
      AND ((CASE WHEN @documentStatus >= 40 THEN 1 ELSE 0 END) = c.[PublishedVersion])
      AND (@expiringSoon = 0
         OR (DATEADD(day, -@conWarningDays1, c.[ExpirationDate]) <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)))
      AND d.[MemberContactKey] = @contentProducerKey
      AND EXISTS(
          SELECT 1
            FROM [dbo].[AccessItem] INNER JOIN [dbo].[UserToken] ON [AccessItem].[Grantee] = [UserToken].[Grantee] OR AccessItem.Grantee = @loggedInUserGroupKey
           WHERE [AccessItem].[AccessKey] = b.[AccessKey]
             AND [UserToken].[UserKey]=@userKey
             AND ([AccessItem].[Permission]&3)>0)
      AND (NOT EXISTS (SELECT 1 FROM [dbo].[UniformLicense] WHERE UniformKey = b.DocumentVersionKey)
         OR EXISTS(
          SELECT 1
            FROM [dbo].[UniformLicense] INNER JOIN [dbo].[LicenseLegacyList] l ON [UniformLicense].[LicenseKey] = l.[LicenseLegacyKey]
           WHERE [UniformLicense].[UniformKey] = b.[DocumentVersionKey]))
    UNION
    SELECT a.[HierarchyKey],
          b.[DocumentKey],
          b.[DocumentVersionKey],
          b.[DocumentStatusCode],
          b.[DocumentName],
          b.[AlternateName],
          b.[DocumentDescription],
          b.[DocumentTypeCode],
          b.[IsSystem],
          b.[ContainsChildrenFlag],
          b.[RelatedDocumentVersionKey],
          b.[AccessKey],
          b.[DefaultChildAccessKey],
          b.[StatusUpdatedOn],
          b.[StatusUpdatedByUserKey],
          b.[CreatedOn],
          b.[CreatedByUserKey],
          b.[UpdatedOn],
          b.[UpdatedByUserKey],
          NULL,
          NULL
     FROM [dbo].[Hierarchy] a INNER JOIN [dbo].[DocumentMain] b ON a.[UniformKey] = b.[DocumentVersionKey]
    WHERE @documentStatus <> 40
      AND b.[DocumentVersionKey] NOT IN (SELECT [DocumentVersionKey] FROM [ContentWorkflowParameters])
      AND b.[DocumentStatusCode] = @documentStatus
      AND b.[DocumentTypeCode] = 'CON'
       AND EXISTS(
          SELECT 1
            FROM [dbo].[AccessItem] INNER JOIN [dbo].[UserToken] ON [AccessItem].[Grantee] = [UserToken].[Grantee] OR AccessItem.Grantee = @loggedInUserGroupKey
           WHERE [AccessItem].[AccessKey] = b.[AccessKey]
             AND [UserToken].[UserKey]=@userKey
             AND ([AccessItem].[Permission]&3)>0)
      AND (NOT EXISTS (SELECT 1 FROM [dbo].[UniformLicense] WHERE UniformKey = b.DocumentVersionKey)
         OR EXISTS(
          SELECT 1
            FROM [dbo].[UniformLicense] INNER JOIN [dbo].[LicenseLegacyList] l ON [UniformLicense].[LicenseKey] = l.[LicenseLegacyKey]
           WHERE [UniformLicense].[UniformKey] = b.[DocumentVersionKey]))
    ORDER BY [DocumentName]
END


GO